import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_gzlxtop10dyxhbjs(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)
    sql = f'''insert into fridge_biz_result.result_gzlxtop10dyxhbjs
                (
                select
                       t1.fault_name,
                       if(t2.model is null,'',t2.model) as model,
                       count(distinct t2.objectid) as model_count,
                       t2.hxjms as spare_parts ,
                       sum(t2.hxjquantity) as spare_parts_count,
                       format_datetime(date('{date}') - interval '1' month, 'YYYY-MM') as time
                from
                    (select
                        if(zzfld00002gt is null,'',zzfld00002gt) as fault_name,
                        count(distinct objectid)  as fault_count
                    FROM
                        ods.kyd_ods
                    where
                        date(p_date)>=(date('{date}') - interval '1' month)
                    and
                        date(p_date) < date('{date}')
                    and
                        zzfld000001='QT30'
                    and
                        zzfld00002ft != '安装'
                    group by
                        if(zzfld00002gt is null,'',zzfld00002gt)
                    order by
                        fault_count desc
                    limit 10) t1
                left join
                    (
                    select
                        t1.objectid,
                        if(t2.material is null,substr(t1.zzfld00000b, 4, 7),t2.material) as material,
                        upper(if(t2.model is null,regexp_extract(zzfld000036,'[a-zA-Z0-9]+-[a-zA-Z0-9]+'),t2.model)) as model,
                        if(zzfld00002gt is null,'',zzfld00002gt) as fault_name,
                        if(t1.hxjms is null,'',t1.hxjms) as hxjms,
                        t1.hxjquantity
                    from
                        ods.kyd_ods t1
                    left join
                        dim.material t2
                    on
                        substr(t1.zzfld00000b, 4, 7)=t2.material
                    where
                        date(t1.p_date)>=(date('{date}') - interval '1' month)
                    and
                        date(t1.p_date) < date('{date}')
                    and
                        t1.zzfld000001='QT30'
                    and
                        t1.zzfld00002ft != '安装'
                ) t2
                on
                    t1.fault_name = t2.fault_name
                where
                    t1.fault_name is not null and t1.fault_name != ''
                and
                    t2.model is not null and t2.model != ''
                group by
                    t1.fault_name,if(t2.model is null,'',t2.model),t2.hxjms
                ) 
                 '''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_gzlxtop10dyxhbjs(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

